﻿using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Dynamic;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Text;
using System.Threading.Tasks;
using System.Xml;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory.Database;

namespace _09_1对多
{
    public static class EFCoreExtentions
    {
        /// <summary>
        ///  查询
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandType"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static IEnumerable<dynamic> ExecuteSqlQuery(this DbContext db, string sql, CommandType commandType = CommandType.Text, params DbParameter[] parameters)
        {
            using (var conn = db.Database.GetDbConnection().CreateCommand())
            {
                conn.CommandText = sql;
                conn.CommandType = commandType;
                if (conn.Connection.State != ConnectionState.Open)
                {
                    conn.Connection.Open(); //打开连接
                }
                //添加输入参数
                conn.Parameters.AddRange(parameters);
              
                using (var dataReader = conn.ExecuteReader())
                {
                    conn.Parameters.Clear();
                    while (dataReader.Read())
                    {
                        IDictionary<string, object> row = new ExpandoObject(); //实例化一个动态可扩展对象
                        for (int i = 0; i < dataReader.FieldCount; i++)
                        {
                            row.Add(dataReader.GetName(i), dataReader[i]);
                        }
                        yield return row;
                           
                    }
                }
                
            }
        }
        /// <summary>
        ///  执行增，删，改的命令
        /// </summary>
        /// <param name="db"></param>
        /// <param name="sql"></param>
        /// <param name="ct"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(this DbContext db, string sql, CommandType ct = CommandType.Text, params DbParameter[] parameters)
        {
            using (var cmd = db.Database.GetDbConnection().CreateCommand())
            {
                try {
                    cmd.CommandText = sql;
                    cmd.CommandType = ct;
                    if (cmd.Connection.State != ConnectionState.Open)
                    {
                        cmd.Connection.Open();
                    }
                    cmd.Parameters.AddRange(parameters);
                   
                    int result=cmd.ExecuteNonQuery();
                    return result;
                }catch (Exception ex)
                {
                    throw;
                }

            }
           
        }

   




    }
}

